package thoven.achievement.net.jsonutil.service

import com.google.gson.GsonBuilder
import com.google.gson.JsonArray
import com.google.gson.JsonElement
import com.google.gson.JsonObject
import com.google.gson.JsonParser

/**
 * JSON ⇌ SQL 转换服务
 */
object SqlConverter {
    
    private val gson = GsonBuilder()
        .setPrettyPrinting()
        .serializeNulls()
        .disableHtmlEscaping()
        .create()
    
    /**
     * JSON 转 SQL INSERT 语句
     */
    fun jsonToInsert(json: String, tableName: String = "table_name"): Result<String> {
        return try {
            val jsonElement = JsonParser.parseString(json)
            
            val result = when {
                jsonElement.isJsonObject -> {
                    generateInsertFromObject(jsonElement.asJsonObject, tableName)
                }
                jsonElement.isJsonArray -> {
                    generateInsertFromArray(jsonElement.asJsonArray, tableName)
                }
                else -> {
                    return Result.failure(IllegalArgumentException("JSON must be an object or array"))
                }
            }
            
            Result.success(result)
        } catch (e: Exception) {
            Result.failure(e)
        }
    }
    
    /**
     * JSON 转 CREATE TABLE 语句
     */
    fun jsonToCreateTable(json: String, tableName: String = "table_name"): Result<String> {
        return try {
            val jsonElement = JsonParser.parseString(json)
            
            if (!jsonElement.isJsonObject) {
                return Result.failure(IllegalArgumentException("JSON must be an object"))
            }
            
            val jsonObject = jsonElement.asJsonObject
            val columns = mutableListOf<String>()
            
            jsonObject.entrySet().forEach { (key, value) ->
                val columnName = sanitizeColumnName(key)
                val columnType = inferSqlType(value)
                columns.add("    $columnName $columnType")
            }
            
            val sql = buildString {
                appendLine("CREATE TABLE $tableName (")
                appendLine(columns.joinToString(",\n"))
                appendLine(");")
            }
            
            Result.success(sql)
        } catch (e: Exception) {
            Result.failure(e)
        }
    }
    
    /**
     * SQL INSERT 转 JSON
     */
    fun insertToJson(sql: String): Result<String> {
        return try {
            val result = parseInsertStatement(sql)
            Result.success(result)
        } catch (e: Exception) {
            Result.failure(e)
        }
    }
    
    /**
     * 从单个对象生成 INSERT 语句
     */
    private fun generateInsertFromObject(jsonObject: JsonObject, tableName: String): String {
        val columns = mutableListOf<String>()
        val values = mutableListOf<String>()
        
        jsonObject.entrySet().forEach { (key, value) ->
            columns.add(sanitizeColumnName(key))
            values.add(formatValue(value))
        }
        
        return buildString {
            append("INSERT INTO $tableName ")
            append("(${columns.joinToString(", ")}) ")
            append("VALUES ")
            append("(${values.joinToString(", ")});")
        }
    }
    
    /**
     * 从数组生成多条 INSERT 语句
     */
    private fun generateInsertFromArray(jsonArray: JsonArray, tableName: String): String {
        if (jsonArray.size() == 0) {
            return "-- Empty array, no INSERT statements generated"
        }
        
        val statements = mutableListOf<String>()
        
        jsonArray.forEach { element ->
            if (element.isJsonObject) {
                statements.add(generateInsertFromObject(element.asJsonObject, tableName))
            }
        }
        
        return statements.joinToString("\n")
    }
    
    /**
     * 推断 SQL 数据类型
     */
    private fun inferSqlType(element: JsonElement): String {
        return when {
            element.isJsonNull -> "VARCHAR(255)"
            element.isJsonPrimitive -> {
                val primitive = element.asJsonPrimitive
                when {
                    primitive.isBoolean -> "BOOLEAN"
                    primitive.isNumber -> {
                        val num = primitive.asNumber
                        if (num.toString().contains(".")) {
                            "DECIMAL(10, 2)"
                        } else {
                            "INT"
                        }
                    }
                    else -> {
                        val str = primitive.asString
                        when {
                            str.length > 255 -> "TEXT"
                            else -> "VARCHAR(255)"
                        }
                    }
                }
            }
            element.isJsonObject -> "JSON"
            element.isJsonArray -> "JSON"
            else -> "VARCHAR(255)"
        }
    }
    
    /**
     * 格式化值为 SQL 格式
     */
    private fun formatValue(element: JsonElement): String {
        return when {
            element.isJsonNull -> "NULL"
            element.isJsonPrimitive -> {
                val primitive = element.asJsonPrimitive
                when {
                    primitive.isBoolean -> primitive.asBoolean.toString().uppercase()
                    primitive.isNumber -> primitive.asNumber.toString()
                    else -> "'${escapeString(primitive.asString)}'"
                }
            }
            element.isJsonObject -> "'${escapeString(element.toString())}'"
            element.isJsonArray -> "'${escapeString(element.toString())}'"
            else -> "NULL"
        }
    }
    
    /**
     * 转义 SQL 字符串
     */
    private fun escapeString(str: String): String {
        return str.replace("'", "''")
            .replace("\\", "\\\\")
    }
    
    /**
     * 清理列名
     */
    private fun sanitizeColumnName(name: String): String {
        var result = name.replace(Regex("[^a-zA-Z0-9_]"), "_")
        
        // 如果以数字开头，添加下划线
        if (result.isNotEmpty() && result[0].isDigit()) {
            result = "_$result"
        }
        
        // 如果是 SQL 关键字，添加反引号
        if (isSqlKeyword(result)) {
            result = "`$result`"
        }
        
        return result
    }
    
    /**
     * 检查是否是 SQL 关键字
     */
    private fun isSqlKeyword(word: String): Boolean {
        val keywords = setOf(
            "select", "from", "where", "insert", "update", "delete", "create",
            "table", "index", "view", "user", "group", "order", "by", "limit",
            "join", "inner", "outer", "left", "right", "on", "as", "and", "or",
            "not", "null", "is", "in", "exists", "between", "like"
        )
        return keywords.contains(word.lowercase())
    }
    
    /**
     * 解析 INSERT 语句转为 JSON
     */
    private fun parseInsertStatement(sql: String): String {
        // 移除多余空格和换行
        val cleanSql = sql.trim().replace(Regex("\\s+"), " ")
        
        // 匹配 INSERT INTO 语句
        val insertPattern = Regex(
            "INSERT\\s+INTO\\s+\\w+\\s*\\(([^)]+)\\)\\s*VALUES\\s*\\(([^)]+)\\)",
            RegexOption.IGNORE_CASE
        )
        
        val matches = insertPattern.findAll(cleanSql)
        val results = mutableListOf<Map<String, Any>>()
        
        matches.forEach { match ->
            val columns = match.groupValues[1]
                .split(",")
                .map { it.trim().replace("`", "") }
            
            val values = parseValues(match.groupValues[2])
            
            if (columns.size == values.size) {
                val obj = mutableMapOf<String, Any>()
                columns.forEachIndexed { index, column ->
                    obj[column] = values[index]
                }
                results.add(obj)
            }
        }
        
        return when {
            results.isEmpty() -> "{}"
            results.size == 1 -> JsonFormatter.format(gson.toJson(results[0])).getOrDefault("{}")
            else -> JsonFormatter.format(gson.toJson(results)).getOrDefault("[]")
        }
    }
    
    /**
     * 解析 VALUES 中的值
     */
    private fun parseValues(valuesStr: String): List<Any> {
        val values = mutableListOf<Any>()
        var current = StringBuilder()
        var inString = false
        var i = 0
        
        while (i < valuesStr.length) {
            val c = valuesStr[i]
            
            when {
                c == '\'' && (i == 0 || valuesStr[i - 1] != '\\') -> {
                    if (inString) {
                        values.add(current.toString())
                        current = StringBuilder()
                        inString = false
                    } else {
                        inString = true
                    }
                }
                c == ',' && !inString -> {
                    if (current.isNotEmpty()) {
                        values.add(parseValue(current.toString().trim()))
                        current = StringBuilder()
                    }
                }
                else -> {
                    if (inString || !c.isWhitespace() || current.isNotEmpty()) {
                        current.append(c)
                    }
                }
            }
            i++
        }
        
        if (current.isNotEmpty()) {
            values.add(parseValue(current.toString().trim()))
        }
        
        return values
    }
    
    /**
     * 解析单个值
     */
    private fun parseValue(str: String): Any {
        return when {
            str.equals("NULL", ignoreCase = true) -> "null"
            str.equals("TRUE", ignoreCase = true) -> true
            str.equals("FALSE", ignoreCase = true) -> false
            str.toIntOrNull() != null -> str.toInt()
            str.toDoubleOrNull() != null -> str.toDouble()
            else -> str
        }
    }
}

